package mappers;

import handler.DatabaseConnection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Club;
import model.Match;

public class MatchMapper {

    private static MatchMapper instance;
    private ArrayList<Match> matches;

    private MatchMapper() {
        matches = new ArrayList<>();
        matches = getTable();

    }

    // Man skal kunne lagre hver event.
    public void insertMatches(ArrayList<Match> matches) {
        try {

            Statement stmt = DatabaseConnection.getDbInstance().getConnection().createStatement();
            //Fjerner alt der tidligere var i tabellen.
            stmt.execute("set foreign_key_checks = 0");
            stmt.execute("delete from game");

            //Her lægges alle elementer fra den indsatte <Match> arrayliste,
            //ind i databasens 'game' tabel.
            String SQL = "insert into game(gameid,hometeam,awayteam,tournamentround,location,isended) values";
            for (int i = 0; i < matches.size(); i++) {
                Match tempMatch = matches.get(i);
                SQL += "(" + tempMatch.getMatchId() + "," + tempMatch.getHomeTeam().getId();
                SQL += "," + tempMatch.getAwayTeam().getId() + "," + tempMatch.getTournamentRound();
                SQL += ",'" + tempMatch.getLocation() + "',0";
                if (i == matches.size() - 1) {
                    SQL += ");";
                } else {
                    SQL += "),\n";
                }
            }
            
            stmt.execute(SQL);
            stmt.close();
        } catch (SQLException ex) {

            System.out.println("Det var ikke muligt at oprette kampene i databasen, kontakt systemadministrator");
        }

    }

    public ArrayList<Match> getTable() {

        try {
            Statement stmt = DatabaseConnection.getDbInstance().getConnection().createStatement();

            String SQL = "Select * from game";
            ResultSet rs = stmt.executeQuery(SQL);

            int count = 0;
            while (rs.next()) {
                int id = rs.getInt("gameid");
                int tournamentRound = rs.getInt("tournamentround");
                String location = rs.getString("location");

                int homeTeamId = rs.getInt("hometeam");
                int awayTeamId = rs.getInt("awayteam");
                Club homeTeam = ClubMapper.getInstance().getClub(homeTeamId);
                Club awayTeam = ClubMapper.getInstance().getClub(awayTeamId);

                boolean isEnded = rs.getBoolean("isended");

                matches.add(new Match(id, homeTeam, awayTeam, location, tournamentRound, isEnded));

                if (isEnded == true) {
                    String result = rs.getString("result");
                    matches.get(count).setResult(result);
                }

                count++;
            }

            stmt.close();
            rs.close();

        } catch (SQLException ex) {
            System.out.println("SQL Fejl: " + ex.getMessage());
        }

        // Returnerer ArrayListen.
        return matches;

    }

    //Henter matches ud fra division og runde.
    public ArrayList<Match> getMatches(int division, int round) {
        ArrayList<Match> selectedMatches = new ArrayList<>();
        try {
            Statement stmt = DatabaseConnection.getDbInstance().getConnection().createStatement();

            String SQL = "Select gameid,tournamentround,location,hometeam,";
            SQL += "awayteam,isended,result from game,club where hometeam= clubid and division";
            SQL += " = " + division + " and tournamentround = " + round;
            ResultSet rs = stmt.executeQuery(SQL);

            int count = 0;
            while (rs.next()) {
                int id = rs.getInt("gameid");
                int tournamentRound = rs.getInt("tournamentround");
                String location = rs.getString("location");

                int homeTeamId = rs.getInt("hometeam");
                int awayTeamId = rs.getInt("awayteam");
                Club homeTeam = ClubMapper.getInstance().getClub(homeTeamId);
                Club awayTeam = ClubMapper.getInstance().getClub(awayTeamId);

                boolean isEnded = rs.getBoolean("isended");

                selectedMatches.add(new Match(id, homeTeam, awayTeam, location, tournamentRound, isEnded));

                if (isEnded == true) {
                    String result = rs.getString("result");

                    selectedMatches.get(count).setResult(result);
                }
                count++;
            }

            stmt.close();
            rs.close();

        } catch (SQLException ex) {
            System.out.println("SQL Fejl: " + ex.getMessage());
        }
        return selectedMatches;
    }

    public static MatchMapper getInstance() {
        if (instance == null) {
            instance = new MatchMapper();
        }
        return instance;
    }

    // Henter en match ud fra id.
    public Match getMatch(int id) {
        //Må godt være null, det ændres uanset hvad, hvis ikke er det en fejl.
        Match match = null;
        try {
            Statement stmt = DatabaseConnection.getDbInstance().getConnection().createStatement();

            String SQL = "select * from game where gameid = " + id;
            ResultSet rs = stmt.executeQuery(SQL);

            while (rs.next()) {

                int gameid = rs.getInt("gameid");
                int tournamentRound = rs.getInt("tournamentround");
                String location = rs.getString("location");

                int homeTeamId = rs.getInt("hometeam");
                int awayTeamId = rs.getInt("awayteam");
                Club homeTeam = ClubMapper.getInstance().getClub(homeTeamId);
                Club awayTeam = ClubMapper.getInstance().getClub(awayTeamId);

                boolean isEnded = rs.getBoolean("isended");

                match = new Match(gameid, homeTeam, awayTeam, location, tournamentRound, isEnded);
                
                if (isEnded == true) {
                    String result = rs.getString("result");
                    match.setResult(result);
                }
            }

            stmt.close();
            rs.close();
        } catch (SQLException ex) {
            System.out.println("1SQL FEJL: " + ex.getMessage());
        }
        return match;
    }

    public void endMatch(Match match) {
        try {
            Statement stmt = DatabaseConnection.getDbInstance().getConnection().createStatement();

            int id = match.getMatchId();

            //Udregner resultatet.
            String result = match.getResult();

            //Sætter isEnded til true i databasen.
            String SQL = "update game set isended = 1 where gameid = " + id;
            stmt.execute(SQL);

            //Sætter resultatet ind i databasen.
            SQL = "update game set result = '" + result + "' where gameid = " + id;
            stmt.execute(SQL);

            stmt.close();
        } catch (SQLException ex) {
            System.out.println("SQL FEJL: " + ex.getMessage());
        }
    }

}
